<?php

/*
 * Copyright (C) 2014 University of Pittsburgh
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

require_once('util/Constants.inc');

class ReferenceObject {
  
  private $keyword_arr;
  private $jurisdiction_arr;
  
  private $acting_agent_arr;
  private $aa_subset_arr;
  private $aa_footnote_arr;
  
  private $participating_agent_arr;
  private $pa_subset_arr;
  private $pa_footnote_arr;
  
  private $emergency_arr;
  private $timeframe_arr;
  private $action_arr;
  private $goal_arr;
  private $prescription_arr;
  private $purpose_arr;
  private $condition_arr;
  
  public function __construct($_keyword_arr = array(),
                              $_jurisdiction_arr = array(),
                              $_acting_agent_arr = array(),
                              $_aa_subset_arr = array(),
                              $_aa_footnote_arr = array(),  
                              $_participating_agent_arr = array(),
                              $_pa_subset_arr = array(),
                              $_pa_footnote_arr = array(),  
                              $_emergency_arr = array(),
                              $_timeframe_arr = array(),
                              $_action_arr = array(),
                              $_goal_arr = array(),
                              $_prescription_arr = array(),
                              $_purpose_arr = array(),
                              $_condition_arr = array()) {
    
    $this->keyword_arr = $_keyword_arr;
    $this->jurisdiction_arr = array_map('intval', array_filter($_jurisdiction_arr, 'is_numeric'));  
    $this->acting_agent_arr = array_map('intval', array_filter($_acting_agent_arr, 'is_numeric'));
    $this->aa_subset_arr = array_map('intval', array_filter($_aa_subset_arr, 'is_numeric'));
    $this->aa_footnote_arr = array_map('intval', array_filter($_aa_footnote_arr, 'is_numeric'));   
    $this->participating_agent_arr = array_map('intval', array_filter($_participating_agent_arr, 'is_numeric'));   
    $this->pa_subset_arr = array_map('intval', array_filter($_pa_subset_arr, 'is_numeric'));
    $this->pa_footnote_arr = array_map('intval', array_filter($_pa_footnote_arr, 'is_numeric'));   
    $this->emergency_arr = array_map('intval', array_filter($_emergency_arr, 'is_numeric')); 
    $this->timeframe_arr = array_map('intval', array_filter($_timeframe_arr, 'is_numeric'));
    $this->action_arr = array_map('intval', array_filter($_action_arr, 'is_numeric'));
    $this->goal_arr = array_map('intval', array_filter($_goal_arr, 'is_numeric'));
    $this->prescription_arr = array_map('intval', array_filter($_prescription_arr, 'is_numeric'));
    $this->purpose_arr = array_map('intval', array_filter($_purpose_arr, 'is_numeric'));
    $this->condition_arr =  array_map('intval', array_filter($_condition_arr, 'is_numeric'));
    
  }

  public function get_doc_sec_inv_sql_statement() {
     
    $sql_statement = 'SELECT T1.doc_sec_id FROM lena.doc_sec_inv T1';
    
    $from_table_arr = array();
    $where_arr = array();
    
    /*
     * lena.doc_sec_acting_agent_lnk
     * 
     * doc_sec_id uuid NOT NULL
     * acting_agent_code integer NOT NULL
     * acting_agent_subset_code integer NOT NULL
     * acting_agent_footnote_code integer NOT NULL
     */
    $agent_in_clause = self::get_in_or_equal_clause($this->acting_agent_arr);
    $subset_in_clause = self::get_in_or_equal_clause($this->aa_subset_arr);
    $footnote_in_clause = self::get_in_or_equal_clause($this->aa_footnote_arr);
    if($agent_in_clause != NULL || $subset_in_clause != NULL || $footnote_in_clause != NULL) {
      $where_arr[] = 'T1.doc_sec_id = T3.doc_sec_id';
      
      if($agent_in_clause != NULL) {
        $where_arr[] = ('T3.acting_agent_code ' . $agent_in_clause);
      }
      if($subset_in_clause != NULL) {
        $where_arr[] = ('T3.acting_agent_subset_code ' . $subset_in_clause);
      }
      if($footnote_in_clause != NULL) {
        $where_arr[] = ('T3.acting_agent_footnote_code ' . $footnote_in_clause);
      }
      
      $from_table_arr[] = 'lena.doc_sec_acting_agent_lnk T3';
    }
    
    /*
     * lena.doc_sec_participating_agent_lnk
     * 
     * doc_sec_id uuid NOT NULL
     * participating_agent_code integer NOT NULL
     * participating_agent_subset_code integer NOT NULL
     * participating_agent_footnote_code integer NOT NULL
     */
    $agent_in_clause = self::get_in_or_equal_clause($this->participating_agent_arr);
    $subset_in_clause = self::get_in_or_equal_clause($this->pa_subset_arr);
    $footnote_in_clause = self::get_in_or_equal_clause($this->pa_footnote_arr);
    if($agent_in_clause != NULL || $subset_in_clause != NULL || $footnote_in_clause != NULL) {
      $where_arr[] = 'T1.doc_sec_id = T4.doc_sec_id';
    
      if($agent_in_clause != NULL) {
        $where_arr[] = ('T4.participating_agent_code ' . $agent_in_clause);
      }
      if($subset_in_clause != NULL) {
        $where_arr[] = ('T4.participating_agent_subset_code ' . $subset_in_clause);
      }
      if($footnote_in_clause != NULL) {
        $where_arr[] = ('T4.participating_agent_footnote_code ' . $footnote_in_clause);
      }
    
      $from_table_arr[] = 'lena.doc_sec_participating_agent_lnk T4';
    }
    
    /*
     * lena.doc_sec_emergency_lnk
     * 
     * doc_sec_id uuid NOT NULL
     * emergency_code integer NOT NULL
     */
    $tmp_in_clause = self::get_in_or_equal_clause($this->emergency_arr);
    if($tmp_in_clause != NULL) {
      $where_arr[] = 'T1.doc_sec_id = T5.doc_sec_id';
      
      $where_arr[] = 'T5.emergency_code ' . $tmp_in_clause;
      $from_table_arr[] = 'lena.doc_sec_emergency_lnk T5';
    }

    /*
     * lena.doc_sec_timeframe_lnk
     * 
     * doc_sec_id uuid NOT NULL
     * timeframe_code integer NOT NULL
     */
    $tmp_in_clause = self::get_in_or_equal_clause($this->timeframe_arr);
    if($tmp_in_clause != NULL) {
      $where_arr[] = 'T1.doc_sec_id = T6.doc_sec_id';
    
      $where_arr[] = ('T6.timeframe_code ' . $tmp_in_clause);
      $from_table_arr[] = 'lena.doc_sec_timeframe_lnk T6';
    }
    
    /*
     * lena.doc_sec_action_lnk
     * 
     * doc_sec_id uuid NOT NULL
     * action_code integer NOT NULL
     */
    $tmp_in_clause = self::get_in_or_equal_clause($this->action_arr);
    if($tmp_in_clause != NULL) {
      $where_arr[] = 'T1.doc_sec_id = T7.doc_sec_id';
    
      $where_arr[] = ('T7.action_code ' . $tmp_in_clause);
      $from_table_arr[] = 'lena.doc_sec_action_lnk T7';
    }
    
    /*
     * lena.doc_sec_goal_lnk
     *
     * doc_sec_id uuid NOT NULL
     * goal_code integer NOT NULL
     */
    $tmp_in_clause = self::get_in_or_equal_clause($this->goal_arr);
    if($tmp_in_clause != NULL) {
      $where_arr[] = 'T1.doc_sec_id = T8.doc_sec_id';
    
      $where_arr[] = ('T8.goal_code ' . $tmp_in_clause);
      $from_table_arr[] = 'lena.doc_sec_goal_lnk T8';
    }
    
    /*
     * Note that prescription code is not in a link table, but is 
     * part of the main table lena.doc_sec_inv
     */
    $tmp_in_clause = self::get_in_or_equal_clause($this->prescription_arr);
    if($tmp_in_clause != NULL) {
      $where_arr[] = ('T1.prescription_code ' . $tmp_in_clause);
    }
    
    /*
     * lena.doc_sec_purpose_lnk
     * 
     * doc_sec_id uuid NOT NULL
     * purpose_code integer NOT NULL
     */
    $tmp_in_clause = self::get_in_or_equal_clause($this->purpose_arr);
    if($tmp_in_clause != NULL) {
      $where_arr[] = 'T1.doc_sec_id = T9.doc_sec_id';
    
      $where_arr[] = ('T9.purpose_code ' . $tmp_in_clause);
      $from_table_arr[] = 'lena.doc_sec_purpose_lnk T9';
    }   
    
    /*
     * lena.doc_sec_condition_lnk
     * 
     * doc_sec_id uuid NOT NULL
     * condition_code integer NOT NULL
     */
    $tmp_in_clause = self::get_in_or_equal_clause($this->condition_arr);
    if($tmp_in_clause != NULL) {
      $where_arr[] = 'T1.doc_sec_id = T10.doc_sec_id';
    
      $where_arr[] = 'T10.condition_code ' . $tmp_in_clause;
      $from_table_arr[] = 'lena.doc_sec_condition_lnk T10';
    }

    foreach($from_table_arr as $from_table) {
      $sql_statement .= (', ' . $from_table);
    }

    for($i = 0; $i < count($where_arr); ++$i) {
      if($i == 0) {
        $sql_statement .= (' WHERE ' . $where_arr[$i]);
      } else {
        $sql_statement .= (' AND ' . $where_arr[$i]);
      }
    }
    
//     $sql_statement .= ' LIMIT ' . Constants::QUERY_LIMIT;
    
    return $sql_statement;
  }
  
  public function get_document_inv_sql_statement($doc_sec_id_arr = array()) {
  
    $sql_statement = 'SELECT DISTINCT T1.document_id FROM lena.document_inv T1';
    
    $from_table_arr = array();
    $where_arr = array();
    
    /*
     * lena.document_doc_sec_lnk
     *
     * document_id uuid NOT NULL,
     * doc_sec_id uuid NOT NULL
     */
    if(is_array($doc_sec_id_arr)) {
      $tmp_in_clause = self::get_in_or_equal_clause($doc_sec_id_arr);
      if($tmp_in_clause != NULL) {
        $where_arr[] = ' T1.document_id = T2.document_id';
        $where_arr[] = 'T2.document_id != \'00000000-0000-0000-0000-000000000000\'';
        $where_arr[] = 'T2.doc_sec_id ' . $tmp_in_clause;
        $from_table_arr[] = 'lena.document_doc_sec_lnk T2';
      }
    }

    /*
     * lena.document_jurisdiction
     * 
     * document_id uuid NOT NULL,
     * jurisdiction_id integer NOT NULL
     */
    $tmp_in_clause = self::get_in_or_equal_clause($this->jurisdiction_arr);
    if($tmp_in_clause != NULL) {
      $where_arr[] = ' T1.document_id = T3.document_id';
    
      $where_arr[] = 'T3.jurisdiction_id ' . $tmp_in_clause;
      $from_table_arr[] = 'lena.document_jurisdiction T3';
    }

    foreach($from_table_arr as $from_table) {
      $sql_statement .= (', ' . $from_table);
    }
    
    for($i = 0; $i < count($where_arr); ++$i) {
      if($i == 0) {
        $sql_statement .= (' WHERE ' . $where_arr[$i]);
      } else {
        $sql_statement .= (' AND ' . $where_arr[$i]);
      }
    }
    
//     $sql_statement .= ' LIMIT ' . Constants::QUERY_LIMIT;
    
    return $sql_statement;
    
  }
  
  public function get_document_inv_keyword_sql_statement($document_id_arr = array(), $is_or = true) {
   
    if($is_or == true) {
      if(is_array($this->keyword_arr) && count($this->keyword_arr) >= 0) {
        $union_sql_arr = array();
        
        foreach($this->keyword_arr as $keyword) {
          $sql_statement = 'SELECT DISTINCT T1.document_id FROM lena.document_inv T1';
          $where_arr = array();
          
          /*
           * lena.document_inv
           *
           * document_id uuid NOT NULL,
           * ...
           */
          if(is_array($document_id_arr)) {
            $tmp_in_clause = self::get_in_or_equal_clause($document_id_arr);
            $where_arr[] = 'T1.document_id ' . $tmp_in_clause;
          }
          
          $where_arr[] = 'LOWER(T1.document_body) LIKE LOWER(\'%' . $keyword . '%\')';
          
          for($i = 0; $i < count($where_arr); ++$i) {
            if($i == 0) {
              $sql_statement .= (' WHERE ' . $where_arr[$i]);
            } else {
              $sql_statement .= (' AND ' . $where_arr[$i]);
            }
          }
          
          $union_sql_arr[] = $sql_statement;
        }
        
        $sql_statement = '';
        
        for($i = 0; $i < count($union_sql_arr); ++$i) {
          if($i == 0) {
            $sql_statement .= $union_sql_arr[$i];
          } else {
            $sql_statement .= (' UNION ' . $union_sql_arr[$i]);
          }
        }
        
//         $sql_statement .= ' LIMIT ' . Constants::QUERY_LIMIT;
        
        return $sql_statement;
      }

    } else {
      
      $sql_statement = 'SELECT DISTINCT T1.document_id FROM lena.document_inv T1';
      $where_arr = array();
      
      /*
       * lena.document_inv
      *
      * document_id uuid NOT NULL,
      * ...
      */
      if(is_array($document_id_arr)) {
        $tmp_in_clause = self::get_in_or_equal_clause($document_id_arr);
        $where_arr[] = 'T1.document_id ' . $tmp_in_clause;
      }
      
      if(is_array($this->keyword_arr) && count($this->keyword_arr) >= 0) {
        foreach($this->keyword_arr as $keyword) {
          $where_arr[] = 'LOWER(T1.document_body) LIKE LOWER(\'%' . $keyword . '%\')';
        }
      }  
      
      for($i = 0; $i < count($where_arr); ++$i) {
        if($i == 0) {
          $sql_statement .= (' WHERE ' . $where_arr[$i]);
        } else {
          $sql_statement .= (' AND ' . $where_arr[$i]);
        }
      }
      
//       $sql_statement .= ' LIMIT ' . Constants::QUERY_LIMIT;
      
      return $sql_statement;
    }
  
  }
  
  private static function get_in_or_equal_clause($in_arr = array()) {
    $tmp_arr = array();
    foreach($in_arr as $value) {
      if($value == -1) {
        return NULL;
      } else {
        $tmp_arr[] = $value;
      }
    }
    
    if(count($tmp_arr) == 0) {
      return NULL;
    }
    
    $tmp_in_clause = '';
    
    if(count($tmp_arr) == 1) {
      return ('= ' . (is_string($tmp_arr[0]) ? '\'' . $tmp_arr[0] . '\'' : $tmp_arr[0]));
    } else {
      foreach($tmp_arr as $value) {
        if($tmp_in_clause === '') {
          $tmp_in_clause = 'IN (' . (is_string($value) ? '\'' . $value . '\'' : $value);
        } else {
          $tmp_in_clause .= (', ' . (is_string($value) ? '\'' . $value . '\'' : $value));
        }
      }
      $tmp_in_clause .= ')';
      return $tmp_in_clause;
    }
  }

}